package tt.dz.biz.dao.batchpay;

import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.common.LogUtil;
import tt.dz.vo.MerchLiabilitiesMonthVO;
import tt.dz.vo.OrderServiceVO;

@Repository("liabilitiesDAO")
public class LiabilitiesDAO
  extends BaseDAO
{
  public static final String INVALID_LIABILITIES_MERCHANT = "update  ms_merch_liabilities_month  set is_valid = 0 where accounting_month=:accountingMonth";
  public static final String SUM_LIABILITIES_MERCHANT = "select mad.merchant_code,mad.merchant_code1,mad.sub_subject_code,sum(amount) as amount, count(1) as total_count,to_char(accounting_date, 'yyyyMM') as accountingMonth  from ms_merch_account_detail mad ,ms_merchant mm ,ms_subject ms ,ms_merchant mm1  where mad.merchant_code = mm.merchant_code   and mad.sub_subject_code = ms.subject_code   and ms.parent_subject_code = '2202'  and mad.merchant_code1 = mm1.merchant_code  and mm1.provider_type in (1,2,3)  and mad.loan_flag = 1  and mm.provider_type = 0  and mad.accounting_date BETWEEN  date(:startDate)  and date(:endDate)  group by mad.merchant_code,mad.merchant_code1,mad.sub_subject_code,to_char(accounting_date, 'yyyyMM') ";
  public static final String INSERT_MERCH_LIABILITIES = "insert into ms_merch_liabilities_month(liabilities_seq,merchant_code,sub_subject_code,merchant_code1,amount,total_count,remark,accounting_month,state,accounting_date,created_time,pay_time,pay_account_date,is_valid)  values(:liabilitiesSeq,:merchantCode,:subSubjectCode,:merchantCode1,:amount,:totalCount,:remark,:accountingMonth,:state,:accountingDate,:createdTime,:payTime,:payAccountDate,:isValid )";
  public static final String QUERY_LIABILITIES_MERCHANT_ALL = "select sf.liabilities_seq, sf.merchant_code,sf.sub_subject_code,sf.merchant_code1,sf.amount,sf.total_count,sf.remark,sf.accounting_month,sf.state,sf.accounting_date,sf.created_time,sf.pay_time,sf.pay_account_date,sf.is_valid,  mm.name as palyerName,mm2.name as receiverName,msSubject.subject_name as subjectName from ms_merch_liabilities_month as sf,ms_merchant mm ,ms_merchant mm2, ms_subject msSubject where sf.merchant_code = mm.merchant_code and sf.merchant_code1=mm2.merchant_code and msSubject.subject_code=sf.sub_subject_code ";
  public static final String PAY_LIABILITIES = "update ms_merch_liabilities_month  set pay_time =  CURRENT_TIMESTAMP , pay_account_date = :payAccountDate,state=:state, trade_no=:tradeNo,updated_by=:updatedBy   where liabilities_seq =:liabilitiesSeq";
  private static final String QUERY_SETTLMENT_SERVICE_BY_SUBJECT = "select osi.serviceno,osi.name from ms_assets_lblts_account_rule mal ,ms_subject ms,order_service_info osi where mal.sub_subject_code = ms.subject_code and mal.service_no = osi.serviceno and osi.isvirtual = 1 and osi.isvalid = 1 and ms.subject_code =:subjectCode";
  
  public List<MerchLiabilitiesMonthVO> queryLiabilitiesByMonthAndMerchant(String yyyyMM, String merchantCode)
  {
    StringBuilder sql = new StringBuilder("select sf.liabilities_seq, sf.merchant_code,sf.sub_subject_code,sf.merchant_code1,sf.amount,sf.total_count,sf.remark,sf.accounting_month,sf.state,sf.accounting_date,sf.created_time,sf.pay_time,sf.pay_account_date,sf.is_valid,  mm.name as palyerName,mm2.name as receiverName,msSubject.subject_name as subjectName from ms_merch_liabilities_month as sf,ms_merchant mm ,ms_merchant mm2, ms_subject msSubject where sf.merchant_code = mm.merchant_code and sf.merchant_code1=mm2.merchant_code and msSubject.subject_code=sf.sub_subject_code ");
    sql.append(" and sf.is_valid = 1 ");
    if (StringUtils.isNotBlank(yyyyMM)) {
      sql.append("  and  sf.accounting_month=:accountingMonth ");
    }
    if (StringUtils.isNotBlank(merchantCode)) {
      sql.append("  and  sf.merchant_code1=:merchantCode1 ");
    }
    sql.append(" order by  sf.created_time asc,sf.merchant_code1 asc,sf.state asc");
    
    MapSqlParameterSource param = new MapSqlParameterSource();
    param.addValue("accountingMonth", yyyyMM);
    param.addValue("merchantCode1", merchantCode);
    return queryForList(sql.toString(), param, MerchLiabilitiesMonthVO.class);
  }
  
  public int updateLiabilities(MerchLiabilitiesMonthVO param)
  {
    return save("update ms_merch_liabilities_month  set pay_time =  CURRENT_TIMESTAMP , pay_account_date = :payAccountDate,state=:state, trade_no=:tradeNo,updated_by=:updatedBy   where liabilities_seq =:liabilitiesSeq", param);
  }
  
  public List<OrderServiceVO> querySettlementServiceBySubjectNo(String subjectNo)
  {
    MapSqlParameterSource param = new MapSqlParameterSource();
    param.addValue("subjectCode", subjectNo);
    return queryForList("select osi.serviceno,osi.name from ms_assets_lblts_account_rule mal ,ms_subject ms,order_service_info osi where mal.sub_subject_code = ms.subject_code and mal.service_no = osi.serviceno and osi.isvirtual = 1 and osi.isvalid = 1 and ms.subject_code =:subjectCode", param, OrderServiceVO.class);
  }
  
  public int deleteLiabilities(String yyyyMM)
    throws Exception
  {
    try
    {
      MapSqlParameterSource param = new MapSqlParameterSource();
      param.addValue("accountingMonth", yyyyMM);
      return save("update  ms_merch_liabilities_month  set is_valid = 0 where accounting_month=:accountingMonth", param);
    }
    catch (Exception e)
    {
      LogUtil.error("更新运营商负债数据为无效失败，参数为：accountingMonth=" + yyyyMM, e);
      throw e;
    }
  }
  
  public int[] insertMerchLiabilities(List<MerchLiabilitiesMonthVO> param)
  {
    return batchSave("insert into ms_merch_liabilities_month(liabilities_seq,merchant_code,sub_subject_code,merchant_code1,amount,total_count,remark,accounting_month,state,accounting_date,created_time,pay_time,pay_account_date,is_valid)  values(:liabilitiesSeq,:merchantCode,:subSubjectCode,:merchantCode1,:amount,:totalCount,:remark,:accountingMonth,:state,:accountingDate,:createdTime,:payTime,:payAccountDate,:isValid )", param);
  }
  
  public List<MerchLiabilitiesMonthVO> sumLiabilities(String startDate, String endDate)
  {
    try
    {
      MapSqlParameterSource param = new MapSqlParameterSource();
      param.addValue("startDate", startDate);
      param.addValue("endDate", endDate);
      return queryForList("select mad.merchant_code,mad.merchant_code1,mad.sub_subject_code,sum(amount) as amount, count(1) as total_count,to_char(accounting_date, 'yyyyMM') as accountingMonth  from ms_merch_account_detail mad ,ms_merchant mm ,ms_subject ms ,ms_merchant mm1  where mad.merchant_code = mm.merchant_code   and mad.sub_subject_code = ms.subject_code   and ms.parent_subject_code = '2202'  and mad.merchant_code1 = mm1.merchant_code  and mm1.provider_type in (1,2,3)  and mad.loan_flag = 1  and mm.provider_type = 0  and mad.accounting_date BETWEEN  date(:startDate)  and date(:endDate)  group by mad.merchant_code,mad.merchant_code1,mad.sub_subject_code,to_char(accounting_date, 'yyyyMM') ", param, MerchLiabilitiesMonthVO.class);
    }
    catch (EmptyResultDataAccessException e)
    {
      LogUtil.error("查询负债数据为空，参数为：startDate=" + startDate + ";endDate=" + endDate, e);
    }
    return null;
  }
}
